package com.authine.cloudpivot.ext.utils;

import com.authine.cloudpivot.ext.dto.QuotationGoodDto;
import com.authine.cloudpivot.ext.dto.VirtualVendorStatisticDto;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

@Slf4j
public class QuotationGoodJdbcUtil {
    private static JdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(JdbcTemplate.class);

    public static int getQuotationGoodCountSql(VirtualVendorStatisticDto good) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT");
        sql.append("\t bjsp.id \n") ;
        sql = getQuotationGoodCommonSql(good, sql);

        getQuotationGoodGroupSql(sql);
        return jdbcTemplate.queryForList(sql.toString()).size();
    }

    public static StringBuffer getQuotationGoodGroupSql(StringBuffer sql){
        sql.append ( "\t group by bjsp.id");
        return  sql;
    }

    private static StringBuffer getQuotationGoodCommonSql(VirtualVendorStatisticDto good, StringBuffer sql) {
        sql.append ("\t from sourcingcenter.i_sourcingcenter_t_quotation_goods bjsp LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods xjsp ON bjsp.inquiry_goods_id = xjsp.id and xjsp.deleted =0\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry xjd ON xjd.id = xjsp.inquiry_id and xjd.deleted =0\n" +
                "\tLEFT JOIN operation1center.i_operation1center_t_platform_grant_user grantUser ON bjsp.vendor_organization_id = grantUser.organization_id  and grantUser.deleted =0\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods_clarify cqjl ON cqjl.inquiry_goods_id = xjsp.id and cqjl.deleted =0 \n" +
                " where bjsp.deleted=0 ") ;
        if (good.getOrganization_company_name()!= null){
            sql.append (" and itgu.vendor_organization_name like '%"+ good.getOrganization_company_name()+"%'");
        }
        if (StringUtils.isNotBlank((good.getBjspIds()))){
            sql.append (" and bjsp.id  in ( '"+ good.getBjspIds()+"' )");
        }
        if (StringUtils.isNotBlank((good.getQuote_count()))){
            sql.append (" and bjsp.quote_count =  '"+good.getQuote_count()+"'");
        }
        if (StringUtils.isNotBlank(good.getQuotation_goodStartTime())){
            sql.append (" and bjsp.createdTime >= '"+good.getQuotation_goodStartTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getQuotation_goodEndTime())){
            sql.append (" and bjsp.createdTime <= '"+good.getQuotation_goodEndTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goodStartTime())){
            sql.append (" and xjsp.createdTime >= '"+good.getInquiry_goodStartTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goodEndTime())){
            sql.append (" and xjsp.createdTime <= '"+good.getInquiry_goodEndTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goods_status())){
            sql.append (" and xjsp.inquiry_goods_status = '"+good.getInquiry_goods_status()+"'");
        }
        return  sql;
    }

    public static List<Map<String, Object>> getQuotationGoodList(VirtualVendorStatisticDto good, Integer limit, Integer size) {
        StringBuffer sql = new StringBuffer();
        sql.append("select xjd.organization_company_name,\n" +
                "\n" +
                "xjd.id inquiry_id,\n" +
                "xjsp.id inquiry_goods_id,\n" +
                "bjsp.vendor_organization_name,\n" +
                "\n" +
                "xjsp.quote_count,\n" +
                "\n" +
                "\txjsp.name,\n" +
                "\txjsp.createdTime xjsp_time,\n" +
                "\txjsp.brand,\n" +
                "\txjsp.model,\n" +
                "\txjsp.num,\n" +
                "\txjsp.unit,\n" +
                "\n" +
                "CASE\n" +
                "\t\tbjsp.quotation_goods_status \n" +
                "\t\tWHEN '0' THEN\n" +
                "\t\t'新建' \n" +
                "\t\tWHEN '1' THEN\n" +
                "\t\t'审核中' \n" +
                "\t\tWHEN '2' THEN\n" +
                "\t\t'已退回' \n" +
                "\t\tWHEN '3' THEN\n" +
                "\t\t'已报价' \n" +
                "\t\tWHEN '4' THEN\n" +
                "\t\t'已还价' \n" +
                "\t\tWHEN '5' THEN\n" +
                "\t\t'已定标' \n" +
                "\t\tWHEN '6' THEN\n" +
                "\t\t'已关闭' \n" +
                "\t\tWHEN '7' THEN\n" +
                "\t\t'已终止' ELSE '' \n" +
                "\tEND AS quotation_goods_status,\n" +
                "  bjsp.price,\n" +
                "\tbjsp.delivery,\n" +
                "\tbjsp.amount,\n" +
                "\tbjsp.address,\n" +
                "\tbjsp.description,\n" +
                "\tbjsp.createdTime,\n" +
                "\tGROUP_CONCAT(cqjl.clarify_tips) clarify_tips " );

        sql = getQuotationGoodCommonSql(good, sql);
        //分组
        getQuotationGoodGroupSql(sql);
        if (limit != null && size != null){
            sql.append( " limit "+limit +","+size);
        }
        log.info("sql:{}",sql);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql.toString());
        return maps;
    }
}
